package cn.edu.klmy.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import web.util.DaoUtil;
import cn.edu.klmy.dao.ITextbookDao;
import cn.edu.klmy.models.course.Textbook;

@Repository
public class TextbookDao extends JdbcDaoSupport implements ITextbookDao {

    private static final String ADD_TEXTBOOK = "INSERT INTO TEXTBOOK(TEXTBOOK_NAME, COURSE_ID) VALUES(?, ?)";

    @Override
    public void addTextbook(Textbook textbook) {
        getJdbcTemplate().update(ADD_TEXTBOOK, new Object[]{textbook.getTextbookName(), textbook.getCourseId()});
    }
    
    private static final String TEXTBOOK_EXISTS = "SELECT COUNT(*) FROM TEXTBOOK WHERE TEXTBOOK_NAME = ?";
    @Override
    public boolean textbookExists(String textbookName) {
        int result = getJdbcTemplate().queryForInt(TEXTBOOK_EXISTS, new Object[]{textbookName});

        if (result < 1) {
            return false;
        }
        return true;
    }

    private static final String UPDATE_TEXTBOOK = "UPDATE TEXTBOOK SET TEXTBOOK_NAME = ?, COURSE_ID=? WHERE ID = ?";

    @Override
    public void updateTextbook(Textbook textbook) {
        getJdbcTemplate().update(UPDATE_TEXTBOOK, new Object[]{textbook.getTextbookName(), textbook.getCourseId(), textbook.getId()});
    }


    public static final String GET_TEXTBOOKS = "SELECT * FROM TEXTBOOK WHERE ";
    @Override
    public List<Textbook> getTextbooks(String fieldName, Object value) {
        String sql = GET_TEXTBOOKS + fieldName + "=" + value;
        return (List<Textbook>) getJdbcTemplate().query(sql, new TextbookMapper());
    }

    private static final String REMOVE_TEXTBOOK = "DELETE FROM TEXTBOOK WHERE ";

    @Override
    public void removeTextbook(String fieldName, Object value) {
    	String sql = REMOVE_TEXTBOOK + fieldName + "=" + value;
        getJdbcTemplate().update(sql);
    }

    @Override
    public List<Textbook> getTextbooks(Map<String, Object[]> filter, int pageNum, int pageSize) {
        String sql = DaoUtil.getFuzzyQueryPageSql("TEXTBOOK", filter, pageNum, pageSize);
        return (List<Textbook>) getJdbcTemplate().query(sql, new Object[]{}, new TextbookMapper());
    }

    @Override
    public int getTextbookCount(Map<String, Object[]> filter) {
        String sql = DaoUtil.getFuzzyQueryCountSql("TEXTBOOK", filter);
        return getJdbcTemplate().queryForInt(sql);
    }

    private static final String ADD_TEXTBOOK_TO_COURSE = "UPDATE TEXTBOOK SET COURSE_ID = ? WHERE ID = ?";
    @Override
    public void addTextbooksToCourse(int[] textbookIds, int courseId) {
        getJdbcTemplate().batchUpdate(ADD_TEXTBOOK_TO_COURSE, new MyBatchPreparedStatementSetter(textbookIds, courseId));
    }

    private static final String REMOVE_TEXTBOOK_FROM_COURSE = "UPDATE TEXTBOOK SET COURSE_ID = NULL WHERE COURSE_ID = ? AND ID = ?";
    @Override
    public void removeTextbooksFromCourse(int[] textbookIds, int courseId) {
        getJdbcTemplate().batchUpdate(REMOVE_TEXTBOOK_FROM_COURSE, new MyBatchPreparedStatementSetter(textbookIds, courseId));
    }

    private static final String REMOVE_ALL_TEXTBOOKS_FROM_COURSE = "UPDATE TEXTBOOK SET COURSE_ID = NULL WHERE COURSE_ID = ?";

    @Override
    public void removeAllTextbooksFromCourse(int courseId) {
        getJdbcTemplate().update(REMOVE_ALL_TEXTBOOKS_FROM_COURSE, new Object[] { courseId });
    }

    private static final String GET_TEXTBOOKS_IN_COURSE = "SELECT TEXTBOOK_NAME, COURSE_ID, ID FROM TEXTBOOK WHERE COURSE_ID = ?";
    @Override
    public List<Textbook> getTextbooksInCourse(int courseId) {
        return (List<Textbook>) getJdbcTemplate().query(GET_TEXTBOOKS_IN_COURSE, new Object[]{ courseId }, new TextbookMapper());
    }

    private static final String GET_TEXTBOOKS_NOT_IN_COURSE = "SELECT TEXTBOOK_NAME, COURSE_ID, ID FROM TEXTBOOK WHERE COURSE_ID IS NULL";
    @Override
    public List<Textbook> getTextbooksNotInCourse() {
        return (List<Textbook>) getJdbcTemplate().query(GET_TEXTBOOKS_NOT_IN_COURSE, new Object[]{}, new TextbookMapper());
    }

    @Resource(name = "dataSource")
    public void setSuperDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    private static class TextbookMapper implements RowMapper<Textbook> {

        @Override
        public Textbook mapRow(ResultSet resultSet, int i) throws SQLException {
            Textbook textbook = new Textbook();
            textbook.setId(resultSet.getInt("ID"));
            textbook.setTextbookName(resultSet.getString("TEXTBOOK_NAME"));
            textbook.setCourseId(resultSet.getInt("COURSE_ID"));
            return textbook;
        }
    }
    private static class MyBatchPreparedStatementSetter implements BatchPreparedStatementSetter {

        private int[] textbookIds;
        private int courseId;

        public MyBatchPreparedStatementSetter(int[] textbookIds, int courseId) {
            this.textbookIds = textbookIds;
            this.courseId = courseId;
        }
        
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            preparedStatement.setInt(1, courseId);
            preparedStatement.setInt(2, textbookIds[i]);
        }

        @Override
        public int getBatchSize() {
            return textbookIds.length;
        }
    }
}
